

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 00 FOLDER SETTINGS
** Definition of Start Point
 clear

* set path
global		path	""	

set maxvar 32767	


* clear
clear
clear 		mata
mat 		drop _all




/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 01 Combine BHC Reporting Data with Bank Betas

*******************************************************************
************* a) Extract bank fundamentals as of 2019Q4  **********

{
** (i) Load FR Y-9C Data
use "$path/0 Data/_Processed/BHC_Bank_Ratios", clear

** Keep only observations as of 2019Q4
keep if (year == 2019 & month == 12)

** Save 
compress
save "$path/0 Data/_Processed/BHC_Bank_Ratios_2019Q4", replace



** (ii) Load Commercial Bank Data
use "$path/0 Data/_Processed/CB_Bank_Ratios", clear

** Keep only observations as of 2019Q4
keep if (year == 2019 & month == 12)

** Save 
compress
save "$path/0 Data/_Processed/CB_Bank_Ratios_2019Q4", replace
}


*******************************************************************
************* b) Extract bank betas as of 2019Q4  *****************

{
** Load Bank Betas 
use "$path/0 Data/_Processed/US_Bank_Betas", clear

** Keep only observations as of 2019Q4
keep if (year(dofm(mdate)) == 2019 & month(dofm(mdate)) == 12)

** Keep only betas (all periods)
keep gvkey permco entity conm mdate bank_beta* bank_real_* rmse* bank_oil_beta* bank_*

rename mdate mdate_beta
gen yq = qofd(dofm(mdate))
	format yq %tq

** Save
compress 
save "$path/0 Data/_Processed/US_Bank_Betas_2019Q4", replace
}


*******************************************************************
************* c) Extract bank stock performance in 2020Q1  ********

{
** Load Daily stock returns
use "$path/0 Data/_Processed/US_Bank_log_returns_daily_1995-2020", clear

** Create unique bank id
egen double bank_id = group(entity permco)

** Create market cap as of end 2019
gen year = year(date)
bys bank_id year: egen max = max(date)
gen mcap = market_cap if year == 2019 & date == max
bys bank_id: egen mcap_2019q1 = max(mcap)

** Keep only data from 2020Q1 until Fed intervention (23 March 2020)
keep if year(date) == 2020 

** Compute log return for each month separately 
gen month = month(date)
bys bank_id month: egen return_jan2020 = total(excess_return) if month == 1
bys bank_id month: egen return_feb2020 = total(excess_return) if month == 2
bys bank_id month: egen return_mar2020 = total(excess_return) if month == 3
bys bank_id month: egen return_apr2020 = total(excess_return) if month == 4
bys bank_id month: egen return_may2020 = total(excess_return) if month == 5
bys bank_id month: egen return_jun2020 = total(excess_return) if month == 6



** Compute log return for 01/01/2020 - 23/03/2020 
*	(i) 01/01/-23/03/2020
bys bank_id: egen return_2020Q1_23_03 	= total(excess_return) if date <= mdy(3,23,2020)
* 	(ii) 01/03/-23/03/2020
bys bank_id: egen return_mar2020_23_03 	= total(excess_return) if date >= mdy(3,01,2020) & date <= mdy(3,23,2020)
* 	(iii) 23/03/-31/07/2020
bys bank_id: egen return_23_03_31_07 	= total(excess_return) if date >= mdy(3,23,2020) & date <= mdy(7,30,2020)
*	(iv) 23/03/-31/07/2020
bys bank_id: egen return_01_01_31_07 	= total(excess_return) if date >= mdy(1,01,2020) & date <= mdy(7,30,2020)
*	(v) 23/03/-30/06/2020
bys bank_id: egen return_23_03_30_06 	= total(excess_return) if date >= mdy(3,23,2020) & date <= mdy(6,30,2020)
* 	(ii) 02/02/-23/03/2020 -- Fahlenbrach period
bys bank_id: egen return_02_02_23_03 	= total(excess_return) if date >= mdy(2,02,2020) & date <= mdy(3,23,2020)

** Reduce to bank level 
keep gvkey permco entity return_* mcap_2019q1
duplicates drop

** Collapse to get one observation per bank
collapse return* mcap_2019q1, by(permco entity gvkey)

replace mcap_2019q1 = mcap_2019q1 / 1000000

** Save
compress
save "$path/0 Data/_Processed/US_Bank_returns_2020", replace
}


********************************************************************
************* d) Extract S&P 500 returns form adjustment in 2020Q1 *

{
** Load daily S&P500 returns
use "$path/0 Data/_Processed/sp500-ret_2000-2020_daily", clear

** Keep only data from 2020Q1 until Fed intervention (23 March 2020)
keep if year(date) == 2020 & month(date) <= 3
drop if date > mdy(3,23,2020)


** Compute log return for 01/01/2020 - 23/03/2020 
egen SP500_return_2020Q1_23_03 = total(return_sp_daily)

** Compute log return for 01/01/2020 - 23/03/2020 
gen month = month(date)
bys month: egen SP500_return_jan2020 = total(return_sp_daily) if month == 1
bys month: egen SP500_return_feb2020 = total(return_sp_daily) if month == 2
bys month: egen SP500_return_mar2020 = total(return_sp_daily) if month == 3


** Reduce to bank level
keep  SP500_return_2020Q1_23_03  SP500_return_jan2020 SP500_return_feb2020 SP500_return_mar2020
duplicates drop

* Collapse to get single observaton
collapse *

** Create artifical date for merging to BHC data as of 2019Q4
gen year 	= 2019
gen month 	= 12

** Save
save "$path/0 Data/_Processed/sp500-return_2020Q1", replace
}




*****************************************************************************
************* e) Extract bank stock performance end of each quarter  ********

{
** Load Daily stock returns
use "$path/0 Data/_Processed/US_Bank_log_returns_daily_1995-2020", clear

** Create unique bank id
egen double bank_id = group(entity permco)


** Create market cap end of each quarter
gen yq = qofd(date)
format yq %tq
	
bys bank_id yq: egen max = max(date)
gen mcap = market_cap if date == max
bys bank_id yq: egen mcap_q = max(mcap)

** Compute log return for each quarter
bys bank_id yq: egen return_2020q1 = total(excess_return) if yq == 240
bys bank_id yq: egen return_2020q2 = total(excess_return) if yq == 241
bys bank_id yq: egen return_2020q3 = total(excess_return) if yq == 242


** Keep only data from 2019 onwards
drop if year(date) < 2019 


** Reduce to bank level 
keep gvkey permco entity yq date return_* mcap_q

drop if gvkey == .
duplicates drop gvkey entity permco yq, force



* replace mcap_q = mcap_q / 1000000

** Save
compress
save "$path/0 Data/_Processed/US_Bank_market_data_2019-2020", replace
}






/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 02 Combine Data from 01

{
** Load 2019Q4 BHC data
use "$path/0 Data/_Processed/BHC_Bank_Ratios_2019Q4", clear 

** Add Commercial Banks
append 	using  "$path/0 Data/_Processed/CB_Bank_Ratios_2019Q4", force

** Add bank betas as of 2019Q4
merge 	1:m entity yq using  "$path/0 Data/_Processed/US_Bank_Betas_2019Q4"
keep 	if _merge == 3
drop 	_merge

** Add Returns for 2020Q1
merge 	m:1 entity using 	"$path/0 Data/_Processed/US_Bank_returns_2020"
keep 	if _merge == 3
drop 	_merge

** Add S&P 500 Returns for bank return adjustment
merge 	m:1 year month using "$path/0 Data/_Processed/sp500-return_2020Q1"
drop 	_merge

** Add SNL Bank types
merge 	m:1 entity using "$path/0 Data/_Processed/SNL_Bank_Types"
drop 	if _merge == 2
drop 	_merge

** Save
compress
save "$path/0 Data/_Processed/Data_for_Crosssection_Test_2020Q1", replace
}



	** This dataset is used in cross sectional regression during COVID-19


* eof




	
	
